package com.rockcent.mall.lib.repository;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.rockcent.common.common.PageResult;
import com.rockcent.common.common.SQLListDoing;
import com.rockcent.common.common.SQLObjectDoing;
import com.rockcent.common.common.SQLPageDoing;
import com.rockcent.common.domain.enumclass.Whether;
import com.rockcent.common.utils.RDateFormatUtils;
import com.rockcent.common.utils.RDateUtils;
import com.rockcent.mall.lib.domain.Merchant;
import com.rockcent.mall.lib.domain.Product;
import com.rockcent.mall.lib.domain.enumclass.*;
import com.rockcent.mall.lib.dto.MerchantDto;
import com.rockcent.mall.lib.dto.ProductDto;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.*;
import java.util.Date;

/**
 * Created by DK on 16/9/18.
 * 产品管理
 */
@Repository
public class MerchantProductRepositoryImpl implements MerchantProductRepositoryCustom {

    private static final Logger log = LoggerFactory.getLogger(MerchantProductRepositoryImpl.class);

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;


    @Autowired
    private ObjectMapper objectMapper;


    @Override
    public PageResult<ProductDto> findMerchantProductPage(String productSourceType, String status, String type, String keyword, String sort, String sortByName, Long merchantId, Long marketMerchantId, PageResult pageResult) {
        SQLPageDoing<ProductDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM product p left join product_quantity pq on p.product_quantity_id = pq.id WHERE 1=1 ");

            if (StringUtils.isNotBlank(keyword)) {
                sql.append(" AND (p.id =:key  OR p.name  LIKE :keyword)");
                params.put("key", keyword);
                params.put("keyword", "%" + keyword + "%");
            }

            if (StringUtils.isNotBlank(productSourceType) && ProductSourceType.SELF == ProductSourceType.valueOf(productSourceType)) {
                sql.append(" AND p.market_id is null ");
            } else if (StringUtils.isNotBlank(productSourceType) && ProductSourceType.MARKET == ProductSourceType.valueOf(productSourceType)) {
                sql.append(" AND p.market_id is not null ");
            }

            if (status != null) {
                if (ProductStatus.valueOf(status) == ProductStatus.WAIT_AUDIT) {
                    sql.append(" AND p.status in (:status) ");
                    params.put("status", Arrays.asList(ProductStatus.AUDIT_FAIL.toString(), ProductStatus.WAIT_AUDIT.toString()));
                } else if (ProductStatus.valueOf(status) == ProductStatus.EXPIRED){
                    sql.append(" AND p.status in (:status)");
                    params.put("status", Arrays.asList(ProductStatus.EXPIRED.toString(), ProductStatus.SUPPLIER_CANCEL_SOLDOUT.toString(), ProductStatus.DISTRIBUTOR_CANCEL_SOLDOUT.toString(), ProductStatus.SUPPLIER_COOPERATION_STOP.toString(), ProductStatus.DISTRIBUTOR_COOPERATION_STOP.toString()));
                }else {
                    sql.append(" AND p.status = :status");
                    params.put("status", status);
                }
            }

            if (marketMerchantId != null) {
                sql.append(" and p.market_merchant_id = :marketMerchantId");
                params.put("marketMerchantId", marketMerchantId);
            }

            if (ProductType.ALL != ProductType.valueOf(type)) {
                sql.append(" AND p.type =:type");
                params.put("type", type);
            }
            if (status == null || ProductStatus.valueOf(status) != ProductStatus.EXPIRED) {
                sql.append(" AND p.is_delete =:isDelete");
                params.put("isDelete", Whether.NO.toString());
            }
            sql.append(" AND p.merchant_id =:merchantId");


            params.put("merchantId", merchantId);

            if (StringUtils.isNotBlank(sortByName)) {
                if ("quantity".equals(sortByName)) {
                    sql.append(" order by pq.").append(sortByName).append(" ").append(sort);
                }else {
                    sql.append(" order by p.").append(sortByName).append(" ").append(sort);
                }
            }else {
                sql.append(" order by p.date_selling desc ");
            }
            fields.append("p.*, pq.quantity");
            log.info("======================>sql{}",sql);
        };

        return doing.go(pageResult, ProductDto.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public PageResult<ProductDto> findSaleMarketPage(String keyword, String type, List<Long> categoryIds, String sortByName, String sort, String showSaleProduct, Long merchantId, Long mallId, PageResult pageResult) {
        SQLPageDoing<ProductDto> doing = (sql, fields, params, typeReferenceMap) -> {

            if (categoryIds != null && categoryIds.size() > 0) {
                sql.append("select %s from (select pc.product_id from product_categories pc where pc.category_id in(:categoryIds) group by pc.product_id) pc left join market_merchant_product mmp on pc.product_id = mmp.product_id left join product p on pc.product_id = p.id left join product_quantity pq on p.product_quantity_id = pq.id where mmp.is_recommend = 1 ");
                params.put("categoryIds", categoryIds);
            } else {
                sql.append("select %s from market_merchant_product mmp left join product p on mmp.product_id = p.id left join product_quantity pq on p.product_quantity_id = pq.id where mmp.is_recommend = 1 ");
            }


            if (StringUtils.isNotBlank(showSaleProduct)) {
                sql.append(" AND p.id not in (select mps.product_id from market_merchant_product_sales mps where mps.distributor_merchant_id =:merchantId or mps.supplier_merchant_id =:merchantId and mps.distributor_status = :distributorStatus and mps.supplier_status = :supplierStatus and mps.is_delete = :isDelete)");
                fields.append("p.*, pq.quantity ");
            } else {
                fields.append("p.*, pq.quantity, (select mps.product_id from market_merchant_product_sales mps where mps.distributor_merchant_id =:merchantId and mps.product_id = mmp.product_id and mps.distributor_status = :distributorStatus and mps.supplier_status = :supplierStatus and mps.is_delete = :isDelete) isSale ");
            }

            if (StringUtils.isNotBlank(keyword)) {
                sql.append(" AND (p.id =:key or p.name like :keyword or p.merchant_name like :keyword)");
                params.put("key", keyword);
                params.put("keyword", "%" + keyword + "%");
            }

            if (ProductType.ALL != ProductType.valueOf(type)) {
                sql.append(" AND p.type =:type");
                params.put("type", type);
            }
            sql.append(" AND (p.date_disabled > :disabled or p.date_disabled is null)");
            sql.append(" AND p.mall_id = :mallId");
            sql.append(" AND p.status = :status");
            params.put("mallId", mallId);
            params.put("status", ProductStatus.SELLING.toString());
            params.put("merchantId", merchantId);
            params.put("disabled", new Date());
            params.put("distributorStatus", MarketMerchantProductSalesStatus.PASS.toString());
            params.put("supplierStatus", MarketMerchantProductSalesStatus.PASS.toString());
            params.put("isDelete", Whether.NO.toString());

            if (StringUtils.isNotBlank(sortByName)) {
                if ("quantity".equals(sortByName)) {
                    sql.append(" order by pq.").append(sortByName).append(" ").append(sort);
                }else {
                    sql.append(" order by p.").append(sortByName).append(" ").append(sort);
                }
            }
        };
        return doing.go(pageResult, ProductDto.class, objectMapper, jdbcTemplate, log);
    }


    @Override
    public List<MerchantDto> findProductRelateMerchant(Long merchantId, String status) {
        SQLListDoing<MerchantDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from product p where p.merchant_id = :merchantId and p.market_id is not null and p.is_delete = :isDelete");
            if (StringUtils.isNotBlank(status)) {
                if (ProductStatus.valueOf(status) == ProductStatus.WAIT_AUDIT) {
                    sql.append(" AND p.status in (:status) ");
                    params.put("status", Arrays.asList(ProductStatus.AUDIT_FAIL.toString(), ProductStatus.WAIT_AUDIT.toString()));
                } else {
                    params.put("status", status);
                    sql.append(" AND p.status = :status");
                }
            }
            sql.append(" group by p.market_merchant_id");
            params.put("merchantId", merchantId);
            params.put("isDelete", Whether.NO.toString());
            fields.append("p.market_merchant_id id, p.market_merchant_name name");
        };
        return doing.go(MerchantDto.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public PageResult<ProductDto> findGiftPacketProductPage(Long merchantId, Long productId, String type, String productSourceType, String sort, String sortByName, String keyword, Date dateEnabled, Date dateDisabled, String maxPrice, String minPrice, List<Long> categoryIds, PageResult pageResult) {
        SQLPageDoing<ProductDto> doing = (sql, fields, params, typeReferenceMap) -> {
            if (categoryIds != null && categoryIds.size() > 0) {
                sql.append("select %s from (select pc.product_id from product_categories pc where pc.category_id in(:categoryIds) group by pc.product_id) pc left join product p on pc.product_id = p.id or pc.product_id = p.market_id left join product_quantity pq on p.product_quantity_id = pq.id  where 1=1 AND p.limit_count = 0 ");
                params.put("categoryIds", categoryIds);
            } else {
                sql.append("select %s from product p left join product_quantity pq on p.product_quantity_id = pq.id where 1=1 AND p.limit_count = 0 ");
            }

            sql.append(" AND p.status = :status");
            params.put("status", ProductStatus.SELLING.toString());

            if (StringUtils.isNotBlank(keyword)) {
                sql.append(" AND (p.id =:key OR p.name  LIKE :keyword OR p.merchant_name like :keyword)");
                params.put("key", keyword);
                params.put("keyword", "%" + keyword + "%");
            }

//            if (dateEnabled != null && dateDisabled != null) {
//                sql.append(" AND (p.date_disabled > :dateDisabled or p.date_disabled is null)");
//                sql.append(" AND (p.date_enabled < :dateEnabled or p.date_enabled is null)");
//                sql.append(" AND p.date_selling < :dateEnabled");
//                params.put("dateEnabled", dateEnabled);
//                params.put("dateDisabled", dateDisabled);
//            }

            if (ProductType.ALL != ProductType.valueOf(type)) {
                sql.append(" AND p.type =:type");
                params.put("type", type);
                if (ProductType.GOODS == ProductType.valueOf(type)) {
                    sql.append(" AND p.shipping_type != :shippingType");
                    params.put("shippingType", ProductShippingType.SHOP_PICKUP.toString());
                }
            } else {
                sql.append(" AND ((p.shipping_type != 'SHOP_PICKUP'  and p.type = 'GOODS') OR p.type = 'SERVICE')");
            }

            if (StringUtils.isNotBlank(productSourceType) && ProductSourceType.SELF == ProductSourceType.valueOf(productSourceType)) {
                sql.append(" AND p.market_id is null ");
            } else if (StringUtils.isNotBlank(productSourceType) && ProductSourceType.MARKET == ProductSourceType.valueOf(productSourceType)) {
                sql.append(" AND p.market_id is not null ");
            }

            if (StringUtils.isNotBlank(maxPrice)) {
                sql.append(" AND p.sell_price <= :maxPrice");
                params.put("maxPrice", maxPrice);
            }

            if (StringUtils.isNotBlank(minPrice)) {
                sql.append(" AND p.sell_price >= :minPrice");
                params.put("minPrice", minPrice);
            }

            if (productId != null) {
                sql.append(" AND p.id != :productId");
                params.put("productId", productId);
            }

            sql.append(" AND p.merchant_id =:merchantId");
            params.put("merchantId", merchantId);

            if (StringUtils.isNotBlank(sortByName)) {
                if ("quantity".equals(sortByName)) {
                    sql.append(" order by pq.").append(sortByName).append(" ").append(sort);
                }else {
                    sql.append(" order by p.").append(sortByName).append(" ").append(sort);
                }
            }
            fields.append("p.*, pq.quantity");
        };
        return doing.go(pageResult, ProductDto.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public PageResult<ProductDto> findGiftPacketMarketProductPage(Long merchantId, Long mallId, Long productId, String type, String sort, String sortByName, String keyword, Date dateEnabled, Date dateDisabled, String maxPrice, String minPrice, List<Long> categoryIds, PageResult pageResult) {
        SQLPageDoing<ProductDto> doing = (sql, fields, params, typeReferenceMap) -> {
            if (categoryIds != null && categoryIds.size() > 0) {
                sql.append("select %s from (select pc.product_id from product_categories pc where pc.category_id in(:categoryIds) group by pc.product_id) pc left join market_merchant_product mmp on pc.product_id = mmp.product_id left join product p on pc.product_id = p.id left join merchant m on mmp.merchant_id = m.id left join product_quantity pq on p.product_quantity_id = pq.id where 1=1 AND p.limit_count = 0 and mmp.is_recommend = 1 and m.prod_audit = 0 ");
                params.put("categoryIds", categoryIds);
            } else {
                sql.append("select %s from market_merchant_product mmp left join product p on mmp.product_id = p.id left join merchant m on mmp.merchant_id = m.id left join product_quantity pq on p.product_quantity_id = pq.id where 1=1 AND p.limit_count = 0 and mmp.is_recommend = 1 and m.prod_audit = 0 ");
            }

            sql.append(" AND p.status = :status");
            params.put("status", ProductStatus.SELLING.toString());
            sql.append(" AND p.id not in (select mps.product_id from market_merchant_product_sales mps where mps.distributor_merchant_id =:merchantId and mps.distributor_status = :distributorStatus and mps.supplier_status = :supplierStatus and mps.is_delete = :isDelete)");

            if (StringUtils.isNotBlank(keyword)) {
                sql.append(" AND (p.id =:key OR p.name  LIKE :keyword OR p.merchant_name like :keyword)");
                params.put("key", keyword);
                params.put("keyword", "%" + keyword + "%");
            }

            if (dateEnabled != null && dateDisabled != null) {
                sql.append(" AND (p.date_enabled < :dateEnabled or p.date_enabled is null)");
                sql.append(" AND (p.date_disabled > :dateDisabled or p.date_disabled is null)");
                sql.append(" AND p.date_selling < :dateEnabled");
                params.put("dateEnabled", dateEnabled);
                params.put("dateDisabled", dateDisabled);
            }

            if (ProductType.ALL != ProductType.valueOf(type)) {
                if (ProductType.GOODS == ProductType.valueOf(type)) {
                    sql.append(" AND p.shipping_type != :shippingType");
                    params.put("shippingType", ProductShippingType.SHOP_PICKUP.toString());
                }
                sql.append(" AND p.type =:type");
                params.put("type", type);
            } else {
                sql.append(" AND ((p.shipping_type != 'SHOP_PICKUP'  and p.type = 'GOODS') OR p.type = 'SERVICE')");
            }

            if (StringUtils.isNotBlank(maxPrice)) {
                sql.append(" AND p.sell_price <= :maxPrice");
                params.put("maxPrice", maxPrice);
            }

            if (StringUtils.isNotBlank(minPrice)) {
                sql.append(" AND p.sell_price >= :minPrice");
                params.put("minPrice", minPrice);
            }

            if (productId != null) {
                sql.append(" AND p.id != :productId");
                params.put("productId", productId);
            }

            sql.append(" AND p.mall_id =:mallId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("distributorStatus", MarketMerchantProductSalesStatus.PASS.toString());
            params.put("supplierStatus", MarketMerchantProductSalesStatus.PASS.toString());
            params.put("isDelete", Whether.NO.toString());

            if (StringUtils.isNotBlank(sortByName)) {
                if ("quantity".equals(sortByName)) {
                    sql.append(" order by pq.").append(sortByName).append(" ").append(sort);
                }else {
                    sql.append(" order by p.").append(sortByName).append(" ").append(sort);
                }
            }
            fields.append("p.*, pq.quantity");
        };
        return doing.go(pageResult, ProductDto.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public List<Merchant> findByProductIds(List<Long> productIds) {
        return null;
    }

    @Override
    public PageResult<Product> searchProduct(ProductDto productDto, Long mallId, ProductStatus status, String keyword, PageResult pageResult) {
        SQLPageDoing<Product> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from product p  ");
            //在综合排序和按销量排的时候再 连表查询
            if (null != productDto.getOrderBy() && (productDto.getOrderBy().equals("saleCount") || productDto.getOrderBy().equals("ALL"))) {
                sql.append(" left join product_statistics ps on p.id = ps.product_id ");
            }
            sql.append(" where 1=1 and market_id is null and p.mall_id =:mallId  and p.status =:status ");
            if (StringUtils.isNotBlank(keyword)) {
                sql.append(" and (p.name like :keyword or p.note like :keyword) ");
                params.put("keyword", "%" + keyword + "%");
            }
            sql.append(" and (p.date_disabled > now() or p.date_disabled is null) ");
            String orderBy = productDto.getOrderBy();
            switch (productDto.getOrderBy()) {
                case "saleCount":
                    orderBy = " order by ps.sale_count " + productDto.getSort();
                    break;
                case "ALL":
                    orderBy = " order by ps.sale_count desc,ps.view_count desc,click_count desc,order_count desc";
                    break;
                case "sellPrice":
                    orderBy = " order by p.sell_price " + productDto.getSort();
                    break;
            }
            sql.append(orderBy);
            params.put("mallId", mallId);
            params.put("status", status.toString());
            fields.append("p.*");
        };
        return doing.go(pageResult, Product.class, objectMapper, jdbcTemplate, log);
    }

    public PageResult<Product> merchantSearch(ProductDto productDto, ProductStatus status, String keyword, PageResult pageResult) {
        SQLPageDoing<Product> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from product p  ");
            //在综合排序和按销量排的时候再 连表查询
            if (null != productDto.getOrderBy() && (productDto.getOrderBy().equals("saleCount") || productDto.getOrderBy().equals("ALL"))) {
                sql.append(" left join product_statistics ps on p.id = ps.product_id ");
            }
            sql.append(" where 1=1 and p.mall_id =:mallId  and p.status =:status  and p.merchant_id =:merchantId ");
            sql.append(" and (p.date_disabled > now() or p.date_disabled is null) ");
            if (StringUtils.isNotBlank(keyword)) {
                sql.append(" and (p.name like :keyword or p.note like :keyword) ");
                params.put("keyword", "%" + keyword + "%");
            }
            String orderBy = productDto.getOrderBy();
            switch (productDto.getOrderBy()) {
                case "saleCount":
                    orderBy = " order by ps.sale_count " + productDto.getSort();
                    break;
                case "ALL":
                    orderBy = " order by ps.sale_count desc,ps.view_count desc,click_count desc,order_count desc";
                    break;
                case "sellPrice":
                    orderBy = " order by p.sell_price " + productDto.getSort();
                    break;
            }
            sql.append(orderBy);
            params.put("mallId", productDto.getMallId());
            params.put("status", status.toString());
            params.put("merchantId", productDto.getMerchantId());
            fields.append("p.*");
        };
        return doing.go(pageResult, Product.class, objectMapper, jdbcTemplate, log);

    }

    /*public List<Product> advisingProducts(Long customerId){
        SQLListDoing<Product> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" SELECT p.* FROM product p WHERE p.id IN ");
            sql.append(" (SELECT c.product_id FROM product_categories c WHERE c.category_id IN ");
            sql.append(" (SELECT pc.category_id FROM ");
            //step1：
            sql.append(" LEFT JOIN  product_categories pc ON pc.product_id = pe1.product_id GROUP BY pc.category_id HAVING pc.`category_id` IS NOT NULL)) ");
            params.put("customerId",customerId);
            fields.append("p.*");

        };
        return doing.go(Product.class, jdbcTemplate, log, objectMapper);
    }
    public List<Product> advisingProducts(Long customerId){
        SQLListDoing<Product> doing = (sql, fields, params, typeReferenceMap) -> {

        };
        return doing.go(Product.class, jdbcTemplate, log, objectMapper);
    }*/


    @Override
    public PageResult<Product> getProductListToMem(List<Long> productIdList, Long mallId, Long merchantId, ProductDto productDto, PageResult pageResult) {
        SQLPageDoing<Product> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from product p ");
            if (null != productDto.getOrderBy() && (productDto.getOrderBy().equals("saleCount") || productDto.getOrderBy().equals("ALL"))) {
                sql.append(" left join product_statistics ps on p.id = ps.product_id ");
            }
            sql.append(" where 1=1 and p.mall_id =:mallId  and p.status = \"SELLING\" ");
            sql.append(" and (p.date_disabled > now() or p.date_disabled is null) ");

            if (null != productIdList && productIdList.size() > 0) {
                sql.append(" and p.id in(:productIdList) ");
                params.put("productIdList", productIdList);
            }

            if (merchantId != null) {
                sql.append(" and p.merchant_id =:merchantId ");
                params.put("merchantId", merchantId);
            }
            String orderBy = productDto.getOrderBy();
            switch (productDto.getOrderBy()) {
                case "saleCount":
                    orderBy = " order by ps.sale_count " + productDto.getSort();
                    break;
                case "ALL":
                    orderBy = " order by ps.sale_count desc,ps.view_count desc,click_count desc,order_count desc";
                    break;
                case "sellPrice":
                    orderBy = " order by p.sell_price " + productDto.getSort();
                    break;
            }
            sql.append(orderBy);
            fields.append("p.*");
            params.put("mallId", mallId);
        };
        return doing.go(pageResult, Product.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public PageResult<Product> getProductListPageResult(ProductDto productDto, PageResult pageResult) {
        SQLPageDoing<Product> doing = (sql, fields, params, typeReferenceMap) -> {
            if (StringUtils.isNotBlank(productDto.getOrderBy()) && productDto.getOrderBy().equals("saleCount")) {
                sql.append("select %s from product p left join product_statistics ds on p.id = ds.product_id ");
                params.put("orderBy", "ds." + productDto.getOrderBy());
            } else {
                sql.append("select %s from product p ");
                params.put("orderBy", "p." + productDto.getOrderBy());
            }
            sql.append("where p.mall_id =:mallId and p.merchant_id =:merchantId and p.status =:status and (p.date_disabled > now() or p.date_disabled is null)  ");
            params.put("mallId", productDto.getMallId());
            params.put("merchantId", productDto.getMerchantId());

            //排序条件,不传默认为dateCreated desc 创建时间降序
            String orderSQL = " order by p.date_created DESC ";
            if (StringUtils.isBlank(productDto.getOrderBy())) {
                //不做变化
            } else if (productDto.getOrderBy().equals("saleCount")) {
                //按销量排
                orderSQL = " order by ds.sale_count ASC ";
            } else if (productDto.getOrderBy().equals("sellPrice")) {
                //按售价排
                orderSQL = " order by p.sell_price " + ("ASC".equalsIgnoreCase(productDto.getSort()) ? "ASC" : "DESC") + " ";
            } else if (productDto.getOrderBy().equals("date_created")) {
                //按时间排
                orderSQL = " order by p.date_created " + ("ASC".equalsIgnoreCase(productDto.getSort()) ? "ASC" : "DESC") + " ";
            }
            sql.append(orderSQL);

            params.put("datetime", new java.sql.Date(System.currentTimeMillis()));
            params.put("status", ProductStatus.SELLING.toString());

            fields.append("p.*");
        };
        return doing.go(pageResult, Product.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public PageResult<Product> getAdvisingProduct(ProductDto productDto, PageResult pageResult) {
        SQLPageDoing<Product> doing = (sql, fields, params, typeReferenceMap) -> {
            //精品推荐：查询
            sql.append("SELECT %s FROM ");
            //表1，
            sql.append("(SELECT * FROM product p LEFT JOIN (SELECT e.product_id ,SUM(e.range_star)AS rang_star FROM evaluation e WHERE is_delete=\"NO\" GROUP BY product_id)  pe ON p.id = pe.product_id where (p.date_disabled > now() or p.date_disabled is null)) a ");
            sql.append(" LEFT JOIN ");
            //表2，
            sql.append(" (SELECT p.id ,pds.click_count,pds.order_count FROM product p LEFT JOIN product_statistics pds ON p.id=pds.product_id WHERE p.status = \"SELLING\" AND pds.is_delete=\"NO\" AND (p.date_disabled > now() or p.date_disabled is null) ) b ");
            //链接条件
            sql.append(" ON a.id = b.id ");
            sql.append(" WHERE a.mall_id= :mallId AND a.merchant_id = :merchantId AND a.is_delete=\"NO\" AND a.status = \"SELLING\"");
            //排序条件
            // 1.好评度最高的排名靠前；
            //2.点击量高的排前；
            //3.下单量高的排前；
            //4.按发布时间排序（无前面数据的时候
            sql.append(" ORDER BY a.rang_star DESC,b.click_count DESC,b.order_count DESC,a.date_created DESC ");
            params.put("mallId", productDto.getMallId());
            params.put("merchantId", productDto.getMerchantId());
            fields.append("a.*,a.id");
        };
        return doing.go(pageResult, Product.class, objectMapper, jdbcTemplate, log);
    }

    public PageResult<Merchant> getOrderProductMerchant(List<Long> productIdList, PageResult pageResult) {
        SQLPageDoing<Merchant> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT DISTINCT %s FROM merchant m LEFT JOIN product p ON m.id = p.merchant_id WHERE p.id IN(:productIdList) ");
            fields.append("m.*");
            params.put("productIdList", productIdList);
        };
        return doing.go(pageResult, Merchant.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public PageResult<ProductDto> getChannelProduct(Long channelId, PageResult pageResult) {
        SQLPageDoing<ProductDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM channel_product cp LEFT JOIN product p ON cp.product_id = p.id LEFT JOIN channel c on cp.channel_id = c.id WHERE cp.channel_id = :channelId and cp.is_delete = \"NO\" ");
            sql.append(" and p.is_delete = \"NO\" and p.status = \"SELLING\"  and (p.date_disabled > now() or p.date_disabled is null) ");
            fields.append("p.*, c.sales_code channelSalesCode");
            params.put("channelId", channelId);
        };
        return doing.go(pageResult, ProductDto.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public PageResult<Product> getMerchantLatestProducts(Long mallId, Long merchantId, PageResult pageResult) {
        SQLPageDoing result = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" SELECT %s FROM product p WHERE p.mall_id=:mallId AND p.merchant_id=:merchantId AND p.status=:status AND p.date_created > :fromDate " +
                    "and p.is_delete = \"NO\" and (p.date_disabled > now() or p.date_disabled is null) ");
            sql.append(" ORDER BY p.date_created DESC");
            params.put("datetime", new java.sql.Date(System.currentTimeMillis()));
            params.put("fromDate", RDateFormatUtils.format(RDateUtils.beforeDate(30)));
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("status", ProductStatus.SELLING.toString());
            fields.append("p.*");
        };
        return result.go(pageResult, Product.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public PageResult<Product> findByIdInAndIsDeletePage(List<Long> productIdList, Long mallId, Whether isDelete, PageResult pageResult) {
        SQLPageDoing result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from product p where 1=1 and p.is_delete = :isDelete and p.status = 'SELLING' and (p.date_disabled > now() or p.date_disabled is null) and p.mall_id = :mallId and p.market_id is null");

            if (productIdList != null && productIdList.size() > 0) {
                sql.append(" and p.id in (:productIdList) ");
                params.put("productIdList", productIdList);
            }
            fields.append("p.*");
            params.put("mallId", mallId);
            params.put("isDelete", isDelete.toString());
        };
        return result.go(pageResult, Product.class, objectMapper, jdbcTemplate, log);
    }

    /**
     *
     * @param mallId
     * @param merchantId
     * @param status
     * @return
     */
    @Override
    public Long getProductWithVerifyStatus(Long mallId, Long merchantId, ProductStatus status) {
        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {
            /**
             * select count(1) from product_audit pa
             left join product p on p.id=pa.product_id
             where p.status='WAIT_AUDIT' and p.mall_id=1 and p.merchant_id=1 and p.is_delete='NO'
             and to_days(now())-to_days(pa.date_created)<=1;
             */
            sql.append("select %s from product p");
            sql.append(" where p.status=:status and p.mall_id=:mallId and p.merchant_id=:merchantId and p.is_delete='NO'");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("status", status.toString());
            fields.append("count(1)");
        };
        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public PageResult<ProductDto> findProductIncomeCollectPage(String productSource, String productType, String beginDate, String endDate, String keyword, Boolean refundable, Long mallId, Long merchantId, PageResult pageResult) {
        SQLPageDoing<ProductDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from product p where 1=1 ");

            if (ProductType.ALL != ProductType.valueOf(productType)) {
                sql.append(" AND p.type = :productType");
                params.put("productType", productType);
            }

            if (StringUtils.isNotBlank(keyword)) {
                sql.append(" AND (p.id = :key OR p.name LIKE :keyword)");
                params.put("key", keyword);
                params.put("keyword", "%" + keyword + "%");
            }

            if (refundable != null) {
                sql.append(" and p.refundable = :refundable");
                params.put("refundable", refundable);
            }

            if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(productSource)) {
                sql.append(" and p.market_id is not null");
            } else if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(productSource)) {
                sql.append(" and p.market_id is null");
            }
            sql.append(" and p.merchant_id = :merchantId");
            sql.append(" and p.mall_id = :mallId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            if (StringUtils.isNotBlank(beginDate) && StringUtils.isNotBlank(endDate)) {
                sql.append(" AND p.date_created < :endDate AND p.date_created > :beginDate");
                params.put("beginDate", RDateUtils.beforeDateStr(beginDate, 0));
                params.put("endDate", RDateUtils.afterDateStr(endDate, 1));
            }
            sql.append(" order by p.date_created desc");
            fields.append("p.id, p.name, p.type, p.refundable, p.base_price, p.serve_rate, p.sell_price, p.market_merchant_name marketMerchantName");
        };
        return doing.go(pageResult, ProductDto.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public Long getLatestProductCountStatisticsYesterDay(Long mallId, Long merchantId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from product p");
            sql.append(" where p.mall_id=:mallId");
            sql.append(" and p.merchant_id=:merchantId");
            sql.append(" and p.is_delete='NO'");
            sql.append(" and p.status=:status");
            sql.append(" and DATE(p.date_created)=CURDATE()-1");
            params.put("status", ProductStatus.SELLING.toString());
            params.put("merchantId", merchantId);
            params.put("mallId", mallId);
            fields.append("count(p.id)");

        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 收藏产品数
     * @param mallId
     * @param merchantId
     * @return
     */
    @Override
    public Long getProductFavoriteCountStatisticsYesterDay(Long mallId, Long merchantId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from favorite");
            sql.append(" where mall_id=:mallId");
            sql.append(" and merchant_id=:merchantId");
            sql.append(" and is_delete='NO'");
            sql.append(" and DATE(date_created)=CURDATE()-1");
            params.put("merchantId", merchantId);
            params.put("mallId", mallId);
            fields.append("count(distinct product_id)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public Long getLatestBuyProductCountStatisticsYesterDay(Long mallId, Long merchantId) {

        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from shopping_cart p");
            sql.append(" where p.mall_id=:mallId");
            sql.append(" and p.merchant_id=:merchantId");
            sql.append(" and p.is_delete='NO'");
            sql.append(" and DATE(p.date_created)=CURDATE()-1");
            params.put("merchantId", merchantId);
            params.put("mallId", mallId);
            fields.append("count(distinct p.product_id)");

        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public Long getNewProductViewCountStatisticsYesterDay(Long mallId, Long merchantId) {

        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from product_event pe");
            sql.append(" left join product p on p.id=pe.product_id");
            sql.append(" where p.mall_id=:mallId");
            sql.append(" and p.merchant_id=:merchantId");
            sql.append(" and pe.is_delete='NO'");
            sql.append(" and pe.event_type='CLICKED'");
            sql.append(" and p.status=:status");
            sql.append(" and DATE(pe.date_created)=CURDATE()-1");
            sql.append(" and DATE(p.date_created)=CURDATE()-1");
            params.put("status", ProductStatus.SELLING.toString());
            params.put("merchantId", merchantId);
            params.put("mallId", mallId);
            fields.append("count(pe.id)");

        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public List<ProductDto> getTop3SellingList(Long mallId, Long merchantId) {
        SQLListDoing<ProductDto> doing = (sql, fields, params, typeReferenceMap) -> {
            /**
             * select pe.product_id,pe.product_name,sum(pe.num) saleCount from product_event pe
             *left join  product p on p.id=pe.product_id
             *left join merchant_order_item moi on moi.product_id=pe.product_id
             *where pe.is_delete='NO'
             *and pe.event_type='SALED'
             *and p.mall_id=1
             *and p.merchant_id=1
             *group by pe.product_id,pe.product_name
             *order by saleCount desc limit 0,3
             */
            sql.append("select %s from merchant_order_item moi");
            sql.append(" left join product p on p.id=moi.product_id");
            sql.append(" where moi.is_delete='NO'");

            sql.append(" and p.mall_id=:mallId and p.merchant_id=:merchantId");
            sql.append(" and DATE(moi.date_created)=CURDATE()-1");
            sql.append(" group by moi.product_id,moi.product_name");
            sql.append(" order by saleCount desc limit 0,3");

            params.put("merchantId", merchantId);
            params.put("mallId", mallId);

            fields.append("moi.product_id as id ,p.name,sum(moi.buy_count) saleCount, (select count(1) from product_event pe where pe.product_id=p.id and DATE(pe.date_created)=CURDATE()-1) pageView");
        };

        return doing.go(ProductDto.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public List<ProductDto> findIncomeCollectSum(String productSource, String productType, String beginDate, String endDate, String keyword, Boolean refundable, Long mallId, Long merchantId) {
        SQLListDoing<ProductDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from product p where 1=1 ");

            if (StringUtils.isNotBlank(keyword)) {
                sql.append(" AND (p.id = :key OR p.name LIKE :keyword)");
                params.put("key", keyword);
                params.put("keyword", "%" + keyword + "%");
            }

            if (ProductType.ALL != ProductType.valueOf(productType)) {
                sql.append(" AND p.type = :productType");
                params.put("productType", productType);
            }

            if (refundable != null) {
                sql.append(" and p.refundable = :refundable");
                params.put("refundable", refundable);
            }

            if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(productSource)) {
                sql.append(" and p.market_id is not null");
            } else if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(productSource)) {
                sql.append(" and p.market_id is null");
            }
            sql.append(" and p.merchant_id = :merchantId");
            sql.append(" and p.mall_id = :mallId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            if (StringUtils.isNotBlank(beginDate) && StringUtils.isNotBlank(endDate)) {
                sql.append(" AND p.date_created < :endDate AND p.date_created > :beginDate");
                params.put("beginDate", RDateUtils.beforeDateStr(beginDate, 0));
                params.put("endDate", RDateUtils.afterDateStr(endDate, 1));
            }
            sql.append(" order by p.date_created desc");
            fields.append("p.*");
        };

        return doing.go(ProductDto.class, jdbcTemplate, log, objectMapper);
    }

    public PageResult<Product> findByMerchantPage(Long merchantId, Whether isDelete, ProductStatus status, PageResult pageResult) {
        SQLPageDoing<Product> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s from product p where p.merchant_id = :merchantId and p.is_delete = :isDelete and p.status = :productStatus and p.market_id is null");
            params.put("merchantId", merchantId);
            params.put("isDelete", isDelete.toString());
            params.put("productStatus", status.toString());
            fields.append("p.*");
        };
        return doing.go(pageResult, Product.class, objectMapper, jdbcTemplate, log);
    }


    @Override
    public List<Long> findAllSoldoutProductIds() {

        SQLListDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from product p ");
            sql.append(" where p.is_delete='NO' ");
            sql.append(" and date_format(date_disabled, '%Y-%m-%d %H:%i') <= date_format(NOW(), '%Y-%m-%d %H:%i') ");
            sql.append(" and (status ='SELLING' OR  status='WAIT_AUDIT' or  status = 'DRAFT')");
            fields.append("distinct  p.id ");

        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);

    }

    public PageResult<Product> findProductByTopicIdPage(Long id, PageResult page) {
        SQLPageDoing<Product> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from topic_product tp left join product p on p.id = tp.product_id where tp.topic_id = :id and tp.is_delete = \"NO\" " +
                    " and p.is_delete = \"NO\" and p.status = \"SELLING\" and (p.date_disabled > now() or p.date_disabled is null) order by seq_no asc ");
            fields.append("p.*");
            params.put("id", id);
        };
        return doing.go(page, Product.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public Product queryByMarketIdAndMerchantIdAndIsDeleteNotInExpired(Long marketId, Long distributorMerchantId, Whether no) {
        SQLObjectDoing<Product> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from product p");
            sql.append(" where market_id= :marketId");
            sql.append(" and is_delete= 'NO'");
            sql.append(" and merchant_id= :merchantId");
            sql.append(" and status <>'EXPIRED'");
            params.put("marketId", marketId);
            params.put("merchantId", distributorMerchantId);
            fields.append("p.*");
        };

        return result.go(Product.class, jdbcTemplate, log, objectMapper);

    }

    @Override
    public PageResult<ProductDto> findProductEvaluationStatisticsPage(Long mallId, Long merchantId, String productSourceType, String keyword, String sortByName, String sort, PageResult page) {
        SQLPageDoing<ProductDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from product p where 1=1 and p.status not in ('AUDIT_FAIL','WAIT_AUDIT','DRAFT')");

            if (StringUtils.isNotBlank(keyword)) {
                sql.append(" AND (p.id =:key  OR p.name  LIKE :keyword)");
                params.put("key", keyword);
                params.put("keyword", "%" + keyword + "%");
            }

            if (StringUtils.isNotBlank(productSourceType) && ProductSourceType.SELF == ProductSourceType.valueOf(productSourceType)) {
                sql.append(" AND p.market_id is null ");
            } else if (StringUtils.isNotBlank(productSourceType) && ProductSourceType.MARKET == ProductSourceType.valueOf(productSourceType)) {
                sql.append(" AND p.market_id is not null ");
            }

            sql.append(" and p.merchant_id = :merchantId");
            sql.append(" and p.mall_id = :mallId");
            if (StringUtils.isNotBlank(sortByName)) {
                sql.append(" order by ").append(sortByName).append(" ").append(sort);
            }else {
                sql.append(" order by goodRate desc");
            }

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            fields.append("p.id, p.name, ");
            fields.append("(select count(1) from evaluation ev where ev.range_star in (4, 5) and ev.product_id = p.id) goodNew, ");
            fields.append("(select count(1) from evaluation eva where eva.range_star = 3 and eva.product_id = p.id ) middleNew, ");
            fields.append("(select count(1) from evaluation eval where eval.range_star in (1, 2) and eval.product_id = p.id ) badNew, ");
            fields.append("(select count(1) from evaluation ev where ev.range_star in (4, 5) and ev.product_id = p.id )/((select count(1) from evaluation ev where ev.range_star in (4, 5) and ev.product_id = p.id ) + (select count(1) from evaluation eva where eva.range_star = 3 and eva.product_id = p.id ) + (select count(1) from evaluation eval where eval.range_star in (1, 2) and eval.product_id = p.id )) goodRate");
        };
        return doing.go(page, ProductDto.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public List<ProductDto> findAllByIds(List<Long> ids) {
        SQLListDoing<ProductDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s from product p  left join product_quantity pq on p.product_quantity_id = pq.id  where p.is_delete='NO' and p.id in (:ids) order by p.date_selling desc");
            params.put("ids", ids);
            fields.append("p.*, pq.quantity");

        };
        return doing.go(ProductDto.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public PageResult<Product> findByMallIdAndIsDeleteOrderByIdDescPage(Long mallId, Whether isDelete, PageResult pageResult) {
        SQLPageDoing result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from product p where 1=1 and p.is_delete = :isDelete and p.status = 'SELLING' and (p.date_disabled > now() or p.date_disabled is null) and p.mall_id = :mallId and p.market_id is null");
            fields.append("p.*");
            params.put("mallId", mallId);
            params.put("isDelete", isDelete.toString());
        };
        return result.go(pageResult, Product.class, objectMapper, jdbcTemplate, log);
    }

    /**
     * 添加精选产品的分页列表
     * @param mallId
     * @param type
     * @param productSourceType
     * @param merchantName
     * @param keyword
     * @param categoryIds
     * @param pageResult
     * @return
     */
    @Override
    public PageResult<ProductDto> findProducts(Long mallId, String type, String productSourceType, String merchantName, String keyword, List<Long> categoryIds, PageResult pageResult, ProductClientType productClientType, String sortByName, String sort) {
        SQLPageDoing<ProductDto> doing = (sql, fields, params, typeReferenceMap) -> {
            if (categoryIds != null && categoryIds.size() > 0) {
                sql.append("select %s from (select pc.product_id from product_categories pc where pc.category_id in(:categoryIds) group by pc.product_id) pc left join product p on pc.product_id = p.id or pc.product_id = p.market_id left join product_quantity pq on p.product_quantity_id = pq.id  where 1=1 ");
                params.put("categoryIds", categoryIds);
            } else {
                sql.append("select %s from product p left join product_quantity pq on p.product_quantity_id = pq.id where 1=1 ");
            }
            sql.append(" and p.mall_id=:mallId");
            params.put("mallId", mallId);
            sql.append(" and p.status = :status");
            params.put("status", ProductStatus.SELLING.toString());
            if(StringUtils.isNotEmpty(keyword)) {
                sql.append(" and (p.id =:key or p.name like :keyword)");
                params.put("key", keyword);
                params.put("keyword", "%" + keyword + "%");
            }
            if(StringUtils.isNotEmpty(merchantName)) {
                sql.append(" and p.merchant_name like :merchantName");
                params.put("merchantName", "%" + merchantName + "%");
            }
            if(StringUtils.isNotEmpty(type) && ProductType.ALL != ProductType.valueOf(type)) {
                sql.append(" AND p.type =:type");
                params.put("type", type);
            }
            if(StringUtils.isNotEmpty(productSourceType)) {
                if (StringUtils.isNotBlank(productSourceType) && ProductSourceType.SELF == ProductSourceType.valueOf(productSourceType)) {
                    sql.append(" AND p.market_id is null ");
                } else if (StringUtils.isNotBlank(productSourceType) && ProductSourceType.MARKET == ProductSourceType.valueOf(productSourceType)) {
                    sql.append(" AND p.market_id is not null ");
                }
            }
            if(productClientType!=null && ProductClientType.EXPLOSION==productClientType) {
                sql.append(" AND p.product_client_type =:productClientType");
                params.put("productClientType", productClientType.toString());
            }
            if(StringUtils.isNotEmpty(sortByName) && StringUtils.isNotEmpty(sort)) {
                if ("quantity".equals(sortByName)) {
                    sql.append(" order by pq.").append(sortByName).append(" ").append(sort);
                }else if("sort_num".equals(sortByName)) {
                    sql.append(" order by p.").append(sortByName).append(" ").append(sort);
                }
            } else {
                sql.append(" order by p.id desc");
            }
            fields.append("p.*, pq.quantity, p.sort_num sortByNum");
        };
        return doing.go(pageResult, ProductDto.class, objectMapper, jdbcTemplate, log);
    }
}
